package usj.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import org.apache.log4j.*;
import org.sqlite.*;

public class CBaseDatos 
{
	static Logger log = Logger.getLogger(CBaseDatos.class);
	String m_rutaBaseDatos=  "./extras/ServMailGrupoB.sqlite";
	Connection m_conexion;
	Statement	m_consulta;
	
	public CBaseDatos(String ruta)
	{
		m_rutaBaseDatos= ruta;
	}
	
	public CBaseDatos()
	{
		
	}
		
	public void conectar()
	{
		 try 
		 {
			  Class.forName("org.sqlite.JDBC");
			  log.info("Conectando a la base de datos");
		 }
		 catch (Exception e) 
		 {
			   System.err.println( e.getMessage());			   
			   log.error(e.getMessage());
		 }  
		 try 
		 {
			 m_conexion = DriverManager.getConnection("jdbc:sqlite:"+m_rutaBaseDatos);
			 m_consulta = m_conexion.createStatement();
			 log.info("Hemos Conectado a la base de datos " + m_rutaBaseDatos);
		 }
		 catch (Exception e) 
		 {
			 System.err.println( e.getMessage());
			 log.error(e.getMessage());
		 }
	}
	
	public boolean insertar(String sql)
	{
		 boolean valor = true;
		  conectar();
		  log.debug(sql);
		  try 
		  {
			  int updateCount = m_consulta.executeUpdate(sql);
			  if(updateCount <= 0)
			  {
				  valor = false;
			  }
		  } 
		  catch (Exception e) 
		  {
			  valor = false;
			  System.err.println( e.getMessage());
			  log.error(e.getMessage());
		  }      
		  finally
		  {  
		   try
		   {    
			   m_consulta.close();  
			   m_conexion.close();  
		   }
		   catch (Exception e)
		   {                 
		     e.printStackTrace(); 
		     log.error(e.getMessage());
		   }  
		  }
		  return valor;
		}
		
		public boolean Eliminar(String sql)
		{
		  boolean valor = true;
		  conectar();
		  log.debug(sql);
		  try 
		  {			  
			  int updateCount = m_consulta.executeUpdate(sql);
			  if(updateCount <= 0)
			  {
				  valor = false;
			  }
		  } 
		  catch (Exception e) 
		  {
			  valor = false;
			  System.err.println( e.getMessage());
			  log.error(e.getMessage());
		  }      
		  finally
		  {  
		   try
		   {    
			   m_consulta.close();  
			   m_conexion.close();  
		   }
		   catch (Exception e)
		   {                 
		     e.printStackTrace();  
		     log.error(e.getMessage());
		   }  
		  }
		  return valor;
		}

		public ResultSet consultar(String sql)
		{
			 conectar();
			 ResultSet resultado = null;
			 log.debug(sql);
			 try 
			 {
			  resultado = m_consulta.executeQuery(sql);
			 }
			 catch (Exception e) 
			 {
				 System.err.println( e.getMessage());
				 log.error(e.getMessage());
			 }			 
			 return resultado;
		}	
		
		public void cerrarConexion()
		{
			try
			   {    
				   m_consulta.close();  
				   m_conexion.close();  
			   }
			   catch (Exception e)
			   {                 
			     e.printStackTrace();  
			     log.error(e.getMessage());
			   }  
		}
		
		public int dameIdUsuario(String user_name)
		{
			int rtn=-1;
			try
			{
				StringBuilder sqlStr=  new StringBuilder();
				sqlStr.append("SELECT id FROM user where user_name like '");
				sqlStr.append(user_name).append("'");
				ResultSet r =consultar(sqlStr.toString());
				rtn=r.getInt(1);
				log.info(rtn);
				if(r.next() )
				{
					rtn=r.getInt(1);					
				}
				else
				{
					rtn=-1;
				}
				cerrarConexion();
				
			}
			catch(Exception e)
			{
				System.err.println( e.getMessage());
				log.error(e.getMessage());
			}
			log.info(rtn);			
			return rtn;				
		}
		
		public int dameIdUsuarioConPassword(String user_name,String user_pass)
		{
			int rtn=-1;
			try
			{
				StringBuilder sqlStr=  new StringBuilder();
				sqlStr.append("SELECT id FROM user where user_name like '").append(user_name).append("' ");
				sqlStr.append(" AND user_pass like '").append(user_pass).append("' ");				
				ResultSet r =consultar(sqlStr.toString());
				if(r.next())
				{
					rtn=r.getInt(1);	
				}
				else
				{
					rtn=-1;
				}
				cerrarConexion();
			}
			catch(Exception e)
			{
				System.err.println( e.getMessage());
				log.error(e.getMessage());
			}
			return rtn;				
		}
		
		public boolean insertamensaje (String msg_from,String msg_to,String msg_sub,String msg_data)
		{
			boolean rtn=false;
			StringBuilder sqlStr=  new StringBuilder();
			sqlStr.append("INSERT INTO msg (user_id,msg_from,msg_to,msg_subject,msg_data,msgs_msg) VALUES ("); 
			sqlStr.append(dameIdUsuario(msg_to)).append(",");
			sqlStr.append("'").append(msg_from).append("'").append(",");
			sqlStr.append("'").append(msg_to).append("'").append(",");
			sqlStr.append("'").append(msg_sub).append("'").append(",");
			sqlStr.append("'").append(msg_data).append("'").append(",");
			sqlStr.append("'").append(" _ ").append("'").append(")");
			rtn= insertar(sqlStr.toString());
			return rtn;
		}
		
		public int dameNumMsgUsuario(String user_name)
		{
			int rtn=-1;
			try
			{	
				int userid=dameIdUsuario(user_name);
				StringBuilder sqlStr=  new StringBuilder();
				sqlStr.append("SELECT count(*) FROM msg where user_id=").append(userid);									
				ResultSet r =consultar(sqlStr.toString());
				if(r.next())
				{
					rtn=r.getInt(1);	
				}
				else
				{
					rtn=-1;
				}
				cerrarConexion();
			}
			catch(Exception e)
			{
				System.err.println( e.getMessage());
				log.error(e.getMessage());
			}
			return rtn;				
		}
		public int dameTamMsgUsuario(String user_name)
		{
			int rtn=-1;
			ResultSet rst;
			try
			{	
				int userid=dameIdUsuario(user_name);
				StringBuilder sqlStr=  new StringBuilder();
				sqlStr.append("SELECT msg_data FROM msg where user_id=").append(userid);
				rst=consultar(sqlStr.toString());
				rtn=0;
				while(rst.next())
				{
					rtn += rst.getString("msg_data").length(); 
				}			
				cerrarConexion();
				
			}
			catch(Exception e)
			{
				System.err.println( e.getMessage());
				log.error(e.getMessage());
			}
			return rtn;				
		}
				
		public String dameListaMsgDeUser(String user)
		{
			int userid=dameIdUsuario(user);			
			StringBuilder rtnMsg=  new StringBuilder();
			StringBuilder sqlStr=  new StringBuilder();
			ResultSet result;
			try
			{
				
				sqlStr.append(" SELECT * FROM msg where ");
				sqlStr.append(" user_id=").append(userid).append(" ");							
				result=consultar(sqlStr.toString());
				rtnMsg.append("+OK").append("\r\n");
				int i=0;
				while(result.next())
				{				
					rtnMsg.append(result.getInt("id")).append(" ").append( result.getString("msg_data").length() ).append("\r\n");
					i++;
					
				}									
				rtnMsg.append(".\r\n");
				
				
				if( i<=0 )				
				{
					rtnMsg = new StringBuilder();
					rtnMsg.append("-ERR message not found.").append("\r\n");					
				}
				cerrarConexion();
				
			}
			catch(Exception e)
			{
				System.err.println( e.getMessage());
				rtnMsg.append("-ERR message not found.").append("\r\n");
				log.error(e.getMessage());
			}									
			return rtnMsg.toString();	
		}
		
		public String dameMsgDeUserConId (String user,int id)
		{					
			int userid=dameIdUsuario(user);			
			StringBuilder rtnMsg=  new StringBuilder();
			StringBuilder sqlStr=  new StringBuilder();
			ResultSet result;
			try
			{
				
				sqlStr.append(" SELECT * FROM msg where ");
				sqlStr.append(" id='").append(id).append("' ");
				sqlStr.append(" AND user_id=").append(userid).append(" ");							
				result=consultar(sqlStr.toString());
				
				if(result.next())
				{
					rtnMsg.append("+OK").append("\r\n");
					rtnMsg.append("MAIL FROM:").append(result.getString("msg_from")).append("\r\n");
					rtnMsg.append("RCPT TO:").append(result.getString("msg_to")).append("\r\n");
					rtnMsg.append("Subject:").append(result.getString("msg_subject")).append("\r\n");
					rtnMsg.append("From:").append(result.getString("msg_from")).append("\r\n");
					rtnMsg.append("To:").append(result.getString("msg_to")).append("\r\n");
					rtnMsg.append("\r\n").append(result.getString("msg_data")).append("\r\n");
					rtnMsg.append(".\r\n");
				}
				else
				{
					rtnMsg.append("-ERR message not found.").append("\r\n");					
				}
				cerrarConexion();
			}
			catch(Exception e)
			{
				System.err.println( e.getMessage());
				rtnMsg.append("-ERR message not found.").append("\r\n");
				log.error(e.getMessage());
			}									
			return rtnMsg.toString();										
		}
		
		public String eliminaMsgDeUserConId (String user,int id)
		{					
			int userid=dameIdUsuario(user);			
			StringBuilder rtnMsg=  new StringBuilder();
			StringBuilder sqlStr=  new StringBuilder();
			boolean result;
			try
			{
				
				sqlStr.append(" delete FROM msg where ");
				sqlStr.append(" id='").append(id).append("' ");
				sqlStr.append(" AND user_id=").append(userid).append(" ");							
				result=Eliminar(sqlStr.toString());
				
				if(result)
				{
					rtnMsg.append("+OK Message deleted").append("\r\n");					
				}
				else
				{
					rtnMsg.append("-ERR message not deleted. The message doesn’t exist.").append("\r\n");					
				}
				
			}
			catch(Exception e)
			{
				System.err.println( e.getMessage());
				rtnMsg.append("-ERR message not deleted. The message doesn’t exist.").append("\r\n");
				log.error(e.getMessage());
			}									
			return rtnMsg.toString();										
		}
}

